# -*- coding: utf-8 -*-

import pymysql
from DBUtils.PooledDB import PooledDB


# 用来操作数据库的类
class Mysql(object):
    def __init__(self):
        self.host = 'localhost'
        self.port = 3306  # 端口号
        self.user = 'root'  # 用户名
        self.password = "123456mesh"  # 密码
        self.db = "MeshDB"  # 库
        self.table = "MeshDB.user"  # 表
        self.table1 = "MeshDB.group"  # 表
        self.table2 = "MeshDB.scene"
        self.table3 = "MeshDB.light_group"
        self.table4 = "MeshDB.scene_light"
        self.table5 = "MeshDB.mesh"
        self.table6 = "MeshDB.light"
        self.table7 = "MeshDB.grant"
        self.table8 = "MeshDB.mesh_grant"
        self.pool = PooledDB(pymysql, mincached=1,
                             maxcached=200,
                             maxconnections=200,
                             blocking=True, host=self.host, user=self.user,
                             passwd=self.password, db=self.db, port=self.port, charset='utf8')


class User(Mysql):

    def query_userid(self, id):
        flag = 0
        sql = "SELECT * FROM " + self.table + " WHERE session_id=" + "'" + id + "';"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            print(results)
            conn.commit()
            if flag > 0:
                return results[0]
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("query_userid failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    # # 插入数据
    # # 传入session_id和user_id，插入一个新用户的信息，失败则返回false
    def add_user(self, user_id, session_id="", avatarurl=""):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        flag2 = 0
        try:
            sql = "INSERT INTO " + self.table + " (user_id) VALUES ('%s');" % (user_id)
            print(sql)
            flag2 = cursor.execute(sql)
            print(flag2)
            conn.commit()
            if 0 < flag2:
                return True
            elif flag2 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("add_user failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    # 删除数据
    # 传入session_id 删除该元组
    # #cursor.execute返回值为受影响的行数
    def drop_user(self, id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        flag = 0
        try:
            flag = cursor.execute("delete from " + self.table + " WHERE session_id=" + "'" + id + "';")
            conn.commit()
            print(flag)
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("drop_user failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    #    if_login(userid), 如果表里面有这个USERID
    #   表示该用户已经登录过，就返回真， 否则返回假
    def if_login(self, id):
        sql = "SELECT * FROM " + self.table + " WHERE user_id=" + "'" + id + "';"
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        flag = 0
        try:
            results = None
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print(results)
            if 0 < flag:
                return results[0]
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("if_login failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def update_sessionid(self, user_id, session_id):
        sql = "UPDATE " + self.table + " SET session_id=" + "'" + session_id + "' WHERE user_id = " + "'" + user_id + "';"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print(results)
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("if_login failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def set_user_info(self, user_id, nickname='', avatarurl='', gender=0, province='', city='', country=''):
        sql = "update " + self.table + " set nickname='%s',avatarurl='%s',gender='%s',province='%s',city='%s',country='%s' where user_id='%s';" % (
        nickname, avatarurl, str(gender), province, city, country, user_id)
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print(results)
            return True
        except Exception as e:
            conn.rollback()
            print("set_user_info:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def get_user_info(self, user_id):
        sql = "select * from user where user_id = '" + str(user_id) + "';"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            return results
        except Exception as e:
            conn.rollback()
            print("get_user_info:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    # def user_by_openid(self,user_id):
    #     sql = "UPDATE " + self.table + " SET session_id=" + "'" + session_id + "' WHERE user_id = " + "'" + user_id + "';"
    #     print(sql)
    #     conn = self.pool.connection()
    #     cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    #     try:
    #         flag = cursor.execute(sql)
    #         print(flag)
    #         results = cursor.fetchall()
    #         conn.commit()
    #         print(results)
    #         if 0 < flag:
    #             return True
    #         elif flag == 0:
    #             return False
    #     except Exception as e:
    #         conn.rollback()
    #         print("if_login failed:", e)
    #         raise e
    #     finally:
    #         cursor.close()
    #         conn.close()


class Mesh(Mysql):
    # 给user的id，查找mesh的id
    def meshs_by_user(self, u_id):
        flag = 0
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "SELECT * FROM " + self.table5 + " WHERE user_id=" + "'" + str(u_id) + "';"
        print(sql)

        results = None
        try:
            flag = cursor.execute(sql)
            conn.commit()
            # print(flag)
            results = cursor.fetchall()
            # print(results)

            if flag > 0:
                return results
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("meshs_by_user failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def mesh_insert(self, n_k, a_k, n_i, a_i, seq, ad, n_a, i_i, ttl, user_id, name, v_a, g_a):
        flag2 = 0
        flag3 = 0
        sql = "INSERT INTO " + self.table5 + " (net_key,app_key,net_index,app_index,seq,address,node_address,iv_index,ttl,user_id,name,virtual_address,group_address)VALUES ('" + n_k + "','" + a_k + "','" + n_i + "','" + a_i + "','" + seq + "','" + ad + "','" + n_a + "','" + i_i + "','" + str(
            ttl) + "','" + user_id + "','" + name + "','" + v_a + "','" + g_a + "')" + ";"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag2 = cursor.execute(sql)
            conn.commit()
            print(flag2)
            flag3 = cursor.execute("select @@identity;")
            results = cursor.fetchall()
            print(results)
            if flag2 == 1:
                return {
                    'id': results[0]['@@identity']
                }
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("mesh_insert failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

        # 更新数据,给定id和新的名字，修改名字name

    def update_name(self, id, name):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table5 + " SET name= '" + name + "' WHERE id=" + str(id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            if 0 < flag1:
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("update_name failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

        # 删除数据  #cursor.execute返回值为受影响的行数

    def delete_mesh(self, id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute("delete from " + self.table5 + " where id=%d;" % (int(id)))
            print("delete from " + self.table6 + " where id=%d;" % (int(id)))
            conn.commit()
            print(flag)
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("delete_mesh failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def update_ivindex(self, m_id, ivindex):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table5 + " SET iv_index= '" + ivindex + "' WHERE id=" + str(m_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            if 0 < flag1:
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("update_ivindex failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def update_seq(self, m_id, seq):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table5 + " SET seq= '" + seq + "' WHERE id=" + str(m_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            if 0 < flag1:
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("update_seq failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def all_control(self, mesh_id, warm, cold, red, green, blue):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table6 + " SET switch=true " + ",cold=" + str(cold) + ",warm=" + str(
            warm) + ",red=" + str(red) + ",green=" + str(green) + ",blue=" + str(blue) + " where m_id =" + str(
            mesh_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            if 0 < flag1:
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("all_control failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def all_off(self, mesh_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table6 + " SET switch=false" + " where m_id =" + str(mesh_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            if 0 < flag1:
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("all_off failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()


class Light(Mysql):
    def lights_of_mesh(self, m_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = 'SELECT * FROM ' + self.table6 + " WHERE m_id=" + str(m_id) + ";"
        print(sql)
        result = None
        flag = 0
        try:
            flag = cursor.execute(sql)
            result = cursor.fetchall()
            conn.commit()
            return result
        except Exception as e:
            conn.rollback()
            print("groups_of_mesh failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def get_node_addr(self, m_id):
        flag = 0
        sql = "SELECT * FROM " + self.table5 + " WHERE id=" + "'" + str(m_id) + "';"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            print(results)
            conn.commit()
            if flag == 1:
                return results[0]
            elif flag > 1:
                return results
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("get_node_addr failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def update_node_addr(self, m_id, newn_na):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table5 + " SET node_address= '" + newn_na + "' WHERE id=" + str(m_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("update_node_addr failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    # 插入数据,插入一个新灯
    def add_light_mesh(self, name, type, cold, warm, red, green, blue, m_id, device_address, uuid, devicekey):
        flag2 = 0
        flag3 = 0
        if (type == "ios"):
            sql = "INSERT INTO " + self.table6 + " (name,cold,warm,red,green,blue,m_id,device_address,ios_uuid,device_key,switch)VALUES ('" + name + "'," + (
                str(cold)) + "," + (str(warm)) + "," + (str(red)) + "," + (str(green)) + "," \
                  + (str(blue)) + "," + (
                      str(m_id)) + ",'" + device_address + "','" + uuid + "','" + devicekey + "',0)" + ";"
        else:
            sql = "INSERT INTO " + self.table6 + " (name,cold,warm,red,green,blue,m_id,device_address,android_uuid,device_key,switch)VALUES ('" + name + "'," + (
                str(cold)) + "," + (str(warm)) + "," + (str(red)) + "," + (str(green)) + "," \
                  + (str(blue)) + "," + (
                      str(m_id)) + ",'" + device_address + "','" + uuid + "','" + devicekey + "',0)" + ";"
        print(sql)

        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag2 = cursor.execute(sql)
            print(flag2)
            flag3 = cursor.execute("select @@identity;")
            results = cursor.fetchall()
            conn.commit()
            print(results)
            if flag2 == 1:
                return {
                    'id': results[0]['@@identity']
                }
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("add_light_mesh failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def update_mesh_netkey(self, m_id, netkey):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        flag1 = 0
        sql = "UPDATE " + self.table5 + " SET net_key= '" + netkey + "' WHERE id=" + str(m_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            print(flag1)
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("update_net_Key failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def update_light_uuid(self, l_id, uuid):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        flag1 = 0
        sql = "UPDATE " + self.table6 + " SET uuid= '" + uuid + "' WHERE id=" + str(l_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            print(flag1)
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("update_light_uuid failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    # 更新数据,给定id color和r，将color的值为r
    def edit_light(self, id, cold, warm, red, green, blue):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table6 + " SET cold=" + str(cold) + ",warm=" + str(warm) + ",red=" + str(
            red) + ",green=" + str(green) + ",blue=" + str(blue) + " WHERE id=" + str(id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            return True
        except Exception as e:
            conn.rollback()
            print(">>>>>>>>>>>> edit_light failed <<<<<<<<<<<<<", e)
            # raise e
            return False
        finally:
            cursor.close()
            conn.close()

        # 更新数据,给定id 修改blue的值为b

    # 删除数据  #cursor.execute返回值为受影响的行数
    def delete_light_mesh(self, id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute("delete from " + self.table6 + " where id=%d;" % (int(id)))
            print("delete from " + self.table6 + " where id=%d;" % (int(id)))
            conn.commit()
            print(flag)
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("delete_light_mesh failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def add_light_to_group(self, g_id, l_id):
        sql = "INSERT INTO " + self.table3 + " (g_id,l_id)VALUES (" + (str(g_id)) + "," + (str(l_id)) + ");"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute(sql)
            print(flag)
            sql1 = "select * from " + self.table2 + " where g_id = " + str(g_id) + ";"
            print(sql1)
            cursor.execute(sql1)
            scenes_of_group = cursor.fetchall()
            print("scenes_of_this_group", scenes_of_group)
            for scene in scenes_of_group:
                print("one scene", scene)
                sql2 = "replace INTO " + self.table4 + " (s_id,l_id,cold,warm,red,green,blue,g_id)VALUES (" + str(
                    scene['id']) + "," + str(l_id) + "," + str(scene['cold']) + "," + str(scene['warm']) + "," + str(
                    scene['red']) + "," + str(scene['green']) + "," + str(scene['blue']) + "," + str(g_id) + ")" + ";"
                print("**", sql2)
                cursor.execute(sql2)
            conn.commit()
            if flag == 1:
                return True
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("add_light_to_group failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def delete_light_of_group(self, g_id, l_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            sql = "delete from " + self.table3 + " WHERE g_id=" + str(g_id) + " and l_id=" + str(l_id) + ";"
            print(sql)
            flag = cursor.execute(sql)
            print(sql)
            print(flag)
            conn.commit()
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("delete_light_of_group failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def rename_light(self, light_id, newn_na):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table6 + " SET name= '" + newn_na + "' WHERE id=" + str(light_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("rename_light failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def open_light(self, light_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table6 + " SET switch=1" + " WHERE id=" + str(light_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print("open_light sql update flag1 :", flag1)
            conn.commit()
            return True

        except Exception as e:
            conn.rollback()
            print(">>>>>>>light_openlight failed<<<<<<<<<<", e)
            # raise e
            return False
        finally:
            cursor.close()
            conn.close()

    def off_light(self, light_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table6 + " SET switch=0" + " WHERE id=" + str(light_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("light_offlight failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()


class Group(Mysql):

    def groups_of_mesh(self, m_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = 'SELECT * FROM ' + self.table1 + " WHERE m_id=" + str(m_id) + ";"
        print(sql)
        result = None
        flag = 0
        try:
            flag = cursor.execute(sql)
            result = cursor.fetchall()
            conn.commit()
            if flag == 0:
                return False
            else:
                return result
        except Exception as e:
            conn.rollback()
            print("groups_of_mesh failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def get_group_addr(self, m_id):
        flag = 0
        sql = "SELECT * FROM " + self.table5 + " WHERE id=" + "'" + str(m_id) + "';"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print(results)
            if flag == 1:
                return results[0]
            elif flag > 1:
                return results
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("get_group_addr failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def update_group_addr(self, m_id, newg_a):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table5 + " SET group_address= '" + newg_a + "' WHERE id=" + str(m_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("update_group_addr failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

        # 更新数据,给定id name

        # # 插入数据,插入一组

    def add_group(self, m_id, name, g_a):
        flag2 = 0
        flag3 = 0
        print("************11111111111111111****************")
        print("m_id = ", m_id)
        print("name = ", name)
        print("g_a = ", g_a)
        sql = "INSERT INTO " + self.table1 + " (name,m_id,group_address)VALUES ('" + name + "','" + (
            str(m_id)) + "','" + g_a + "')" + ";"
        print("*******************************************")
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag2 = cursor.execute(sql)
            print(flag2)
            flag3 = cursor.execute("select @@identity;")
            results = cursor.fetchall()
            conn.commit()
            print(results)
            if flag2 == 1:
                return {
                    'id': results[0]['@@identity']
                }
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("add_group failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def rename_group(self, id, new_name):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        flag1 = 0
        sql = "UPDATE " + self.table1 + " SET name= '" + new_name + "' WHERE id=" + str(id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            print(flag1)
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("rename_group failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def rename_scene(self, id, new_name):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        flag1 = 0
        sql = "UPDATE " + self.table2 + " SET name= '" + new_name + "' WHERE id=" + str(id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            print(flag1)
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("rename_scene failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def delete_group(self, id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute("delete from " + self.table1 + " where id=%d;" % (int(id)))
            print("delete from " + self.table1 + " where id=%d;" % (int(id)))
            conn.commit()
            print(flag)
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("delete_group failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def add_scene(self, m_id, g_id, name, warm, cold, red, green, blue):
        flag2 = 0
        sql = "INSERT INTO " + self.table2 + " (name,switch,cold,warm,red,green,blue,m_id,g_id)VALUES ('" + name + "',0," + (
            str(cold)) + "," + (str(warm)) + "," + (str(red)) + "," + (str(green)) + "," + (str(blue)) + "," + (
                  str(m_id)) + "," + (str(g_id)) + ")" + ";"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag2 = cursor.execute(sql)
            print(flag2)
            cursor.execute("select @@identity;")
            results = cursor.fetchall()
            print(results)
            if flag2 == 1:
                new_scene_id = results[0]['@@identity']
                sql3 = "select l_id from " + self.table3 + " where g_id =" + str(g_id) + ";"
                cursor.execute(sql3)
                results = cursor.fetchall()
                for light in results:
                    sql4 = "INSERT INTO " + self.table4 + " (s_id,l_id,cold,warm,red,green,blue,g_id)VALUES (" + str(
                        new_scene_id) + "," + str(light['l_id']) + "," + str(cold) + "," + str(warm) + "," + str(
                        red) + "," + str(green) + "," + str(blue) + "," + str(g_id) + ")" + ";"
                    cursor.execute(sql4)
                    conn.commit()
                return {
                    'id': new_scene_id
                }
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("add_scene failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def delete_scene(self, s_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "delete from " + self.table2 + " where id=%d;" % (int(s_id))
        print("删除场景：", sql)
        try:
            flag = cursor.execute(sql)
            conn.commit()
            print(flag)
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("delete_scene failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def edit_scene(self, s_id, cold, warm, red, green, blue):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table2 + " SET cold=" + str(cold) + ",warm=" + str(warm) + ",red=" + str(
            red) + ",green=" + str(green) + ",blue=" + str(blue) + " WHERE id=" + str(s_id) + ";"
        sq2 = "UPDATE " + self.table4 + " SET cold=" + str(cold) + ",warm=" + str(warm) + ",red=" + str(
            red) + ",green=" + str(green) + ",blue=" + str(blue) + " WHERE s_id=" + str(s_id) + ";"
        print(sql)
        print(sq2)
        try:
            flag1 = cursor.execute(sql)
            flag2 = cursor.execute(sq2)
            conn.commit()
            if flag1 > 0:  # flag2可能为0因为可能场景中没有灯
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("edit_scene failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def lights_of_scene(self, scene_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "SELECT B.name as name,B.id as lightid,B.device_address as nodeaddress,A.warm as warm, A.cold as cold, A.red as red, A.green as green, A.blue as blue FROM scene_light as A join light as B on B.id = A.l_id where s_id = " + str(
            scene_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            result = cursor.fetchall()
            print("@@@", result)
            return result
        except Exception as e:
            conn.rollback()
            print("edit_scene failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def edit_light_in_scene(self, sceneid, lightid, groupid, warm, cold, red, green, blue):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = ("REPLACE INTO scene_light(s_id,l_id,g_id,cold,warm,red,green,blue) VALUES(" + str(sceneid) + "," + str(
            lightid) + "," + str(groupid) + "," + str(warm) + "," + str(cold) + "," + str(
            red) + "," + str(green) + "," + str(blue) + ");")
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            result = cursor.fetchall()
            return result
        except Exception as e:
            conn.rollback()
            print("edit_light_in_scene failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def apply_scene(self, s_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        sql = "UPDATE " + self.table2 + " SET switch=1 WHERE id=" + str(s_id) + ";"
        print(sql)
        try:
            cursor.execute(sql)
            conn.commit()
            return True
        except Exception as e:
            conn.rollback()
            print("apply_scene failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def off_scene(self, s_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        flag1 = 0

        sql = "UPDATE " + self.table2 + " SET switch=0 WHERE id=" + str(s_id) + ";"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            print(flag1)
            conn.commit()
            print(flag1)
            if 1 == flag1:
                return True
            else:
                return False

        except Exception as e:
            conn.rollback()
            print("off_scene failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def scenes_of_group(self, g_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = 'SELECT * FROM ' + self.table2 + " WHERE g_id=" + str(g_id) + ";"
        print(sql)
        result = None
        flag = 0
        try:
            flag = cursor.execute(sql)
            result = cursor.fetchall()
            conn.commit()
            if flag == 0:
                return False
            else:
                return result
        except Exception as e:
            conn.rollback()
            print("scenes_of_group failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def lights_in_group(self, g_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = 'SELECT * FROM ' + self.table6 + ' AS A JOIN (SELECT * FROM ' + self.table3 + " WHERE g_id=" + str(
            g_id) + ")as B on A.id= B.l_id;"
        print("找出组中的灯：", sql)
        result = None
        flag = 0
        try:
            flag = cursor.execute(sql)
            result = cursor.fetchall()
            print("找出在该组中的灯：", result)
            conn.commit()
            return result
        except Exception as e:
            conn.rollback()
            print("lights_in_group failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def find_group_mesh(self, g_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = 'SELECT * FROM ' + self.table1 + " WHERE id=" + str(g_id) + ";"
        print(sql)
        result = None
        flag = 0
        try:
            flag = cursor.execute(sql)
            result = cursor.fetchall()
            conn.commit()
            if flag == 0:
                return False
            else:
                return result[0]
        except Exception as e:
            conn.rollback()
            print("find_group_mesh failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def lights_out_group(self, g_id):
        result = self.find_group_mesh(g_id)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        print(result)
        print("m_id is", result['m_id'], type(result['m_id']))
        m_id = result['m_id']
        sql = 'SELECT * FROM ' + self.table6 + " WHERE m_id =" + str(
            m_id) + " and id not in(select l_id from MeshDB.light_group Where g_id = " + str(g_id) + ");"
        print(sql)
        result1 = None
        flag = 0
        try:
            flag = cursor.execute(sql)
            result1 = cursor.fetchall()
            conn.commit()
            return result1
        except Exception as e:
            conn.rollback()
            print("lights_out_mesh failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def group_control(self, group_id, warm, cold, red, green, blue):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table6 + " SET switch=true " + ",cold=" + str(cold) + ",warm=" + str(
            warm) + ",red=" + str(red) + ",green=" + str(green) + ",blue=" + str(
            blue) + " where id in" + "(select l_id from light_group where g_id=" + str(group_id) + ");"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            if 0 < flag1:
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("group_control failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def group_off(self, group_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        global flag1
        sql = "UPDATE " + self.table6 + " SET switch=false" + " where id in" + "(select l_id from light_group where g_id=" + group_id + ");"
        print(sql)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            if 0 < flag1:
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("group_off failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()


class Grant(Mysql):
    def meshs_granted_to_me(self, B_id):
        flag = 0
        sql = "SELECT * FROM " + self.table5 + " join " + self.table8 + " on id = m_id WHERE B_id='" + str(B_id) + "';"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print(results)
            return results
        except Exception as e:
            conn.rollback()
            print("meshs_granted_to_me failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def get_member_granted(self, userid):
        flag = 0
        sql = "SELECT * FROM user join " + self.table7 + " on user_id = B_id WHERE A_id=" + "'" + str(userid) + "';"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print(results)
            return results

        except Exception as e:
            conn.rollback()
            print("get_member_granted failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def get_granted_name(self, userid):
        flag = 0
        sql = "SELECT * FROM user where user_id='" + str(userid) + "';"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print("get_granted_name", results)
            return results[0]['nickname']

        except Exception as e:
            conn.rollback()
            print("get_granted_name failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def get_my_meshs_granted_to_someone(self, A_id, B_id):
        sql = "SELECT * FROM " + self.table5 + " join " + self.table8 + " ON id = m_id WHERE B_id=" + "'" + str(
            B_id) + "'and A_id=" + "'" + str(A_id) + "';"
        print("get_my_meshs_granted_to_someone", sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print(results)
            return results

        except Exception as e:
            conn.rollback()
            print("get_my_meshs_granted_to_someone failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def get_my_meshs_ungranted_to_someone(self, A_id, B_id):
        # sql = "SELECT * FROM"+self.table5+" join "+self.table5+"on Mesh.user_id=" + "'" + str(A_id) +"'"+" where m_id not in (select m_id from mesh_grant where A_id="+"'"+str(A_id)+"'"+"and B_id="+"'"+str(B_id)+ "');"
        sql = "SELECT * FROM " + self.table5 + " where user_id=" + "'" + str(
            A_id) + "'" + " and id not in (select m_id from mesh_grant where A_id=" + "'" + str(
            A_id) + "'" + "and B_id=" + "'" + str(B_id) + "');"

        print("get_my_meshs_ungranted_to_someone", sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag = cursor.execute(sql)
            print(flag)
            results = cursor.fetchall()
            conn.commit()
            print(results)
            return results

        except Exception as e:
            conn.rollback()
            print("get_my_meshs_ungranted_to_someone failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def rename_granted_member(self, A_id, B_id, grantee_newname):

        global flag1
        sql = "UPDATE " + self.table7 + " SET nickname= '" + str(grantee_newname) + "'" + " where A_id= '" + str(
            A_id) + "' and B_id= '" + str(B_id) + "'" + ";"
        print("rename_granted_member sql", sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag1 = cursor.execute(sql)
            conn.commit()
            if 0 < flag1:
                return True
            elif flag1 == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("grant_rename failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def delete_member(self, A_id, B_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "delete from " + self.table7 + " where A_id = '%s' and B_id = '%s';" % (str(A_id), str(B_id))
        print(sql)
        try:
            flag = cursor.execute(sql)
            conn.commit()
            print(flag)
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("delete_memeber failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def add_grant_member(self, A_id, B_id, nickname):
        sql = "REPLACE INTO " + self.table7 + " (A_id,B_id,nickname) VALUES ('" + str(A_id) + "','" + str(
            B_id) + "','" + (nickname) + "');"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag2 = cursor.execute(sql)
            print(flag2)
            flag3 = cursor.execute("select @@identity;")
            results = cursor.fetchall()
            print(results)
            conn.commit()
            if flag2 == 1:
                return {
                    'id': results[0]['@@identity']
                }
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("add_grant_mesh failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def delete_grant_mesh(self, A_id, B_id, m_id):
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "delete from " + self.table8 + " where A_id='%s' and B_id='%s' and m_id='%s';" % (A_id, B_id, m_id)
        print("delete_grant_mesh------", sql)
        try:
            flag = cursor.execute(sql)
            conn.commit()
            print(flag)
            if 0 < flag:
                return True
            elif flag == 0:
                return False
        except Exception as e:
            conn.rollback()
            print("delete_grant_memeber failed", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    def add_mesh_to_member(self, A_id, B_id, m_id):
        flag2 = 0
        flag3 = 0
        print("----------------------", m_id)
        sql = "INSERT INTO " + self.table8 + " (A_id,B_id,m_id) VALUES ('" + str(A_id) + "','" + str(B_id) + "','" + (
            str(m_id)) + "');"
        print(sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = None
        try:
            flag2 = cursor.execute(sql)
            print(flag2)
            flag3 = cursor.execute("select @@identity;")
            results = cursor.fetchall()
            print(results)
            conn.commit()
            if flag2 == 1:
                return {
                    'id': results[0]['@@identity']
                }
            else:
                return False
        except Exception as e:
            conn.rollback()
            print("add_grant_mesh failed:", e)
            raise e
        finally:
            cursor.close()
            conn.close()

    # 创建数据库操作类的实例


mySQLCommand4 = Group()
# mySQLCommand4.connectMysql()

# 创建数据库操作类的实例
mySQLCommand3 = Light()
# mySQLCommand3.connectMysql()


# 创建数据库操作类的实例
mySQLCommand1 = User()
# mySQLCommand1.connectMysql()

# 创建数据库操作类的实例
mySQLCommand2 = Mesh()
# mySQLCommand2.connectMysql()
